package cn.le.service;

import cn.hutool.core.collection.CollectionUtil;
import cn.hutool.core.util.IdUtil;
import cn.hutool.poi.excel.ExcelUtil;
import cn.hutool.poi.excel.sax.handler.RowHandler;
import cn.le.beans.dto.ExcelHomeSearchDto;
import cn.le.beans.vo.ExcelHomeVo;
import cn.le.dao.ExcelHomeMapper;
import cn.le.entity.ExcelHome;
import cn.le.utils.SqlUtils;
import com.github.pagehelper.Page;
import com.github.pagehelper.PageHelper;
import org.apache.poi.openxml4j.opc.OPCPackage;
import org.apache.poi.xssf.eventusermodel.XSSFReader;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.util.CollectionUtils;
import org.springframework.util.StringUtils;
import org.springframework.web.multipart.MultipartFile;

import java.io.ByteArrayInputStream;
import java.io.ByteArrayOutputStream;
import java.io.InputStream;
import java.math.BigDecimal;
import java.util.*;

/**
 * @Description
 * @Author le
 * @Date 2020/2/16 12:52
 * @Version 0.1
 **/
@Service
public class ExcelHomeService {

    @Autowired
    private ExcelHomeMapper homeInfoMapper;

    /**
     * 新增房源
     * @param file
     */
    public void newAdd(MultipartFile file) throws Exception {
        int startRowIndex = 2;
        List<List<Object>> excelDates = new ArrayList<>();
        ExcelUtil.readBySax(file.getInputStream(),-1, new RowHandler() {
            @Override
            public void handle(int i, int i1, List<Object> list) {
                if (i1 < startRowIndex){
                    return;
                }
                if (CollectionUtil.isEmpty(list)){
                    return;
                }
                if (CollectionUtil.isEmpty(list) || list.size() > 16){
                    throw new RuntimeException("格式错误");
                }
                excelDates.add(list);
            }
        });
        List<ExcelHome> homeExcels = new ArrayList<>();
        Set<String> homeAddress = homeInfoMapper.findAddress();
        excelDates.forEach(datas->{
            String address = String.valueOf(datas.get(2));
            //排除已经存在的房源
            if (homeAddress.contains(address)){
                return;
            }
            address = address.substring(0,address.indexOf("区") + 1);
            address = address.replace("成都市","");
            ExcelHome homeInfo = homeInfo = getHomeExcel(address,datas);
            homeExcels.add(homeInfo);
        });
        if (CollectionUtil.isNotEmpty(homeExcels)){
            homeInfoMapper.batchInsert(homeExcels);
        }
    }

    /**
     *总房源
     * @param file
     */
    public void replace(MultipartFile file) throws Exception {

        ByteArrayOutputStream swapStream = new ByteArrayOutputStream();
        InputStream inputStream = file.getInputStream();
        byte[] buff = new byte[5120];
        int rc = 0;
        while ((rc = inputStream.read(buff, 0, 100)) > 0) {
            swapStream.write(buff, 0, rc);
        }
        OPCPackage pkg = OPCPackage.open(new ByteArrayInputStream(swapStream.toByteArray()));
        XSSFReader xssfReader = new XSSFReader(pkg);
        XSSFReader.SheetIterator sheets = (XSSFReader.SheetIterator) xssfReader.getSheetsData();
        Map<Integer,String> sheetMap = new HashMap<>();
        int index = 0;
        while (sheets.hasNext()) { //遍历sheet
            sheets.next();
            String sheetName = sheets.getSheetName();
            sheetMap.put(index,sheetName);
            index++;
            //
        }
        //Excel中的数据
        Map<String,List<List<Object>>> excelDataMap = new HashMap<>();
        //默认从下标为3(第四行)的行数开始读取数据
        final Integer[] startRowIndex = {3};
        ExcelUtil.readBySax(new ByteArrayInputStream(swapStream.toByteArray()),-1, new RowHandler() {
            @Override
            public void handle(int i, int i1, List<Object> list) {
                if (i == 0){
                    return;
                }
                if (i1 == 0 && CollectionUtils.isEmpty(list)){
                    startRowIndex[0] = 4;
                }
                if (i1 < startRowIndex[0]){
                    return;
                }
                if (CollectionUtil.isEmpty(list)){
                    return;
                }
                if (list.size() > 16){
                    throw new RuntimeException("Excel格式错误");
                }
                String sheetName = sheetMap.get(i);
                List<List<Object>> data = excelDataMap.get(sheetName);
                if (CollectionUtil.isEmpty(data)){
                    data = new ArrayList<>();
                    excelDataMap.put(sheetName,data);
                }
                data.add(list);
            }
        });

        List<ExcelHome> homeExcels = new ArrayList<>();
        excelDataMap.forEach((sheetName,datas)->{
            datas.forEach(data->{
                ExcelHome homeInfo = getHomeExcel(sheetName,data);
                homeExcels.add(homeInfo);
            });
        });
        if (CollectionUtils.isEmpty(homeExcels)){
            throw new RuntimeException("解析错误");
        }
        homeInfoMapper.batchInsert(homeExcels);
    }

    public ExcelHome getHomeExcel(String sheetName, List<Object> list)  {
        if (CollectionUtil.isEmpty(list) || list.size() < 15){
            return null;
        }
        ExcelHome homeExcel = new ExcelHome();
        homeExcel.setId(IdUtil.createSnowflake(1,1).nextId());
        //小区
        homeExcel.setName(String.valueOf(list.get(1)));
        //地址
        homeExcel.setAddress(String.valueOf(list.get(2)));
        //地区
        homeExcel.setRegion(sheetName);
        //年代
        String years = String.valueOf(list.get(3));
        homeExcel.setYears("未知".equals(years) ? 0 : Integer.valueOf(years));
        //楼层
        String floor = String.valueOf(list.get(4));
        String oldFloor = floor;
        floor = floor.replace("楼", "").replace("号", "").replace("未知", "")
                .replace("、",",").replace("栋","").trim();
        if (floor.indexOf("负") > -1) {
            floor = floor.replace("负", "");
            if (floor.indexOf("-") > -1) {
                floor = "-" + floor.split("-")[0];
            } else if (floor.indexOf(",") > 0) {
                floor = "-" + floor.split(",")[0];
            }
        } else if (floor.indexOf("-") > -1) {
            floor = floor.split("-")[0];
        } else if (floor.indexOf(",") > -1) {
            floor = "-" + floor.split(",")[0];
        }else if (floor.indexOf("跃") > -1){
            floor = floor.split("跃")[0];
        }

        if (StringUtils.isEmpty(floor)){
            homeExcel.setFloor(0);
        }else {
            homeExcel.setFloor(Integer.valueOf(floor));
        }
        homeExcel.setOldFloor(oldFloor);

        //装修
        homeExcel.setRenovation(String.valueOf(list.get(5)));
        //户型
        String houseType = String.valueOf(list.get(6));
        homeExcel.setHouseType(charToNumber(houseType));
        //建面
        String area = String.valueOf(list.get(7));
        if (area.indexOf("未知") > -1){
            homeExcel.setArea(0d);
        }else {
            homeExcel.setArea(Double.valueOf(area));
        }
        //起拍价
        homeExcel.setStartingPrice(Double.valueOf(String.valueOf(list.get(8))));
        //评估价
        homeExcel.setValuationPrice(Double.valueOf(String.valueOf(list.get(9))));
        //保证金
        homeExcel.setDeposit(list.get(10) == null ? 0 : Double.valueOf(String.valueOf(list.get(10))));
        //折扣
        Double d = Double.valueOf(String.valueOf(list.get(11)));
        BigDecimal b = new BigDecimal(d);
        d = b.setScale(2, BigDecimal.ROUND_HALF_DOWN).doubleValue();
        homeExcel.setDiscount(d);

        String auctionNumber = String.valueOf(list.get(12));
        if ("变卖".equals(auctionNumber)){
            homeExcel.setAuctionNumber(0);
        }else {
            auctionNumber = auctionNumber.replace("次", "");
            homeExcel.setAuctionNumber(Integer.valueOf(charToNumber(auctionNumber)));
        }

        //拍卖时间
        homeExcel.setAuctionTime((Date) list.get(13));
        String metro = String.valueOf(list.get(14));
        if (metro != null && !"null".equals(metro)){
            homeExcel.setMetro(String.valueOf(metro));
        }
        return homeExcel;
    }

    public String charToNumber(String str){
        str = str.replace("一","1");
        str = str.replace("二","2");
        str = str.replace("两","2");
        str = str.replace("三","3");
        str = str.replace("四","4");
        str = str.replace("五","5");
        str = str.replace("六","6");
        str = str.replace("七","7");
        str = str.replace("八","8");
        str = str.replace("九","9");
        str = str.replace("十","10");
        return str;
    }

    /**
     * Excel房源信息检索
     * @param req
     * @return
     */
    public Page<ExcelHomeVo> search(ExcelHomeSearchDto req){
        String param = SqlUtils.getSql(req);
        PageHelper.startPage(req.getPageNum(), req.getPageSize());
        return (Page<ExcelHomeVo>) homeInfoMapper.find(param);
    }

}
